Background - 2 excel files, file1 where the macro makes the changes, file2 (volume-log.txt) contains the data which goes into file1 plus the formatting changes. The macro is currently attached to personal.xlsb
Issue - When I run the macro the macro throws the following error "Compile Error Sub or Function not defined", Where else I need to define the sub VolumeInfo() ??
******************************************************
Sub VolumeInfo()
'
'
' Keyboard Shortcut: Ctrl+z
'
Windows("volume-log.txt").Activate
Columns("A:A").EntireColumn.AutoFit
Cells.Replace What:=" bytes free", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("B2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(FIND(""Checking"",R[-1]C[-1])),R[-1]C,RIGHT(R[-1]C[-1],LEN(R[-1]C[-1])-9))"
Range("C2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(SEARCH(""Dir(s)"",RC[-2])),"""",IF(ISERROR(FIND("":\"",R[-1]C[-2])),"""",R[-1]C[-2]))"
Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",MID(RC[-3],SEARCH("" "",RC[-3],20),20)/1024/1024/1024)"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]<>"""",""Keep"",""Delete"")"
Range("B9000:E9000").Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("volume-log").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("volume-log").Sort.SortFields.Add Key _
:=Range("C2:C9000"), SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("volume-log").Sort
.SetRange Range("B2:E9000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.Copy
Windows("file1.xlsx").Activate
Sheets("free space").Select
Range("E2").Select
Windows("volume-log.txt").Activate
************************************************************